using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;

namespace TPDSTAMS
{
    public class TAMSDBAccess
    {
		protected string _Connection = null;

        /// <summary>
        /// TAMS DB Access constructor. Requires connection string.
        /// </summary>
        public TAMSDBAccess( string DBConnectionString )
        {
            _Connection = DBConnectionString;
        }

        /// <summary>
        /// Based on Consignment vendor and consignment goods no, find goods no for general goods.
        /// </summary>
        public int? GetGoodNoFromGeneralGoods( string consignmentVendorNumber, string consignmentGoodNumber )
        {
            // This is what we return 
            int? goodNo = null;

            SqlConnection conn = new SqlConnection( _Connection );
            SqlDataReader reader = null;

            try
            {
                SqlCommand cmd = new SqlCommand( @"SELECT g.GoodNo FROM dbo.Goods g JOIN dbo.GeneralGoods gg on g.GoodGuid = gg.GoodGuid 
                        WHERE (gg.ConsignmentVendorNumber = @VendorNumber and gg.ConsignmentGoodNumber = @GoodNumber) ", conn );

                cmd.Parameters.AddWithValue( "@VendorNumber", consignmentVendorNumber );
                cmd.Parameters.AddWithValue( "@GoodNumber", consignmentGoodNumber );

                conn.Open( );
                reader = cmd.ExecuteReader( );

                while( reader.Read( ) )
                {
                    goodNo = reader.GetInt32( 0 );

                    // We only take the first one.
                    break;
                }
            }
            catch( Exception ex )
            {
                throw ex;
            }
            finally
            {
                if( reader != null )
                { reader.Close( ); }
                conn.Close( );
            }

            return goodNo;
        }

	}
}

